import datetime
import tkinter
import tkinter.scrolledtext
import tkinter.messagebox
import tkinter.filedialog
import tkinter.simpledialog
import tkinter.ttk
import socket
import sqlite3
import random
import threading
import time
import struct
import os
import re
import tkinter.font as tkFont
from openpyxl import load_workbook
from PIL import ImageGrab
from PIL import Image,ImageTk
import http.server
import socketserver




# ============================通用功能代码开始=====================================##

# 获取本机IP
serverIP = socket.gethostbyname(socket.gethostname())
if serverIP.startswith('127.0.'):
    addrs = socket.getaddrinfo(socket.gethostname(), None, 0, socket.SOCK_STREAM)
    addrs = [x[4][0] for x in addrs]
    serverIP = [x for x in addrs if ':' not in x][0]
IP = serverIP[:serverIP.rindex('.')] + '.255'

# 广播，发送服务端IP地址
def sendServerIP():
    # 创建socket对象
    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    sock.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
    while True:
        # 3秒钟发送一次信息
        sock.sendto('ServerIP'.encode(), (IP, 10000))
        time.sleep(3)


thread_sendServerIP = threading.Thread(target=sendServerIP).start()


def get_image(filename,width,height):  #背景插图用，转换图片格式
    im=Image.open(filename).resize((width,height))
    return  ImageTk.PhotoImage(im)



class Common:
    # 查询数据库，获取学生专业列表
    def getZhuanye():
        with sqlite3.connect('database.db') as conn:
            cur = conn.cursor()
            cur.execute('SELECT DISTINCT(zhuanye) FROM students')
            temp = cur.fetchall()

        xueshengZhuanye = []
        for line in temp:
            xueshengZhuanye.append(line[0])
        return xueshengZhuanye

    #获取实验名列表
    def getShiyan():
        with sqlite3.connect('database.db') as conn:
            cur = conn.cursor()
            cur.execute('SELECT DISTINCT(shiyanming) FROM dianming')
            temp = cur.fetchall()

        xueshengShiyan = []
        for line in temp:
            xueshengShiyan.append(line[0])
        return xueshengShiyan


    # 获取指定专业的学生名单
    def getXuehaoXingming(zhuanye):
        with sqlite3.connect('database.db') as conn:
            cur = conn.cursor()
            cur.execute("select xuehao,xingming from students where zhuanye='" + zhuanye + "' order by xuehao")
            temp = cur.fetchall()

        xueshengXinxi = []
        for line in temp:
            xueshengXinxi.append(line[0] + ',' + line[1])
        return xueshengXinxi


    # 获取指定SQL语句查询结果
    def getDataBySQL(sql):
        with sqlite3.connect('database.db') as conn:
            cur = conn.cursor()
            cur.execute(sql)
            result = cur.fetchall()
        return result

    # 执行SQL语句
    def doSQL(sql):
        with sqlite3.connect('database.db') as conn:
            cur = conn.cursor()
            cur.execute(sql)
            conn.commit()


            # 当前日期时间，格式为“年-月-日 时:分:秒”

    def getCurrentDateTime():
            return str(datetime.datetime.now())[:19]

            # 当前日期时间之前一个半小时前的时间，主要用来避免重复点名

    def getStartDateTime():
            now = datetime.datetime.now()
            now = now + datetime.timedelta(minutes=-90)
            return str(now)[:19]

## ========================通用功能代码结束=====================================##



##=================================界面处理代码============================##
root = tkinter.Tk( )

root.geometry('360x250+400+200')
root['background']='#FFFFFF'




##  背景样式
png='./picture/1.jpg'             #主界面背景图片
im = Image.open(png)
photo = ImageTk.PhotoImage(im)
out = im.resize((360,370), Image.ANTIALIAS)  # resize image with high-quality
out.save(png)
im = Image.open(png)
photo = ImageTk.PhotoImage(im)

Lab= tkinter.Label(root,compound='center',image=photo,width=360,height=370)
Lab.pack()#设置主界面
backtype='#F5F5F5'                      #按钮背景颜色
fonttype=("黑体",9,tkFont.BOLD)           #按钮字体类型


#     lable和entry初始化
# 不允许改变窗口大小
root.resizable(False, False)
root.title('课堂教学管理系统')
shiyanmingcheng= tkinter.StringVar(root)
shiyanmingcheng.set('')
fenzurenshu = tkinter.StringVar(root)
fenzurenshu.set('')
labelshiyanmingcheng = tkinter.Label(root, text='实验名称：')
labelshiyanmingcheng['background']='#FFFFFF'
labelshiyanmingcheng.place(x=20, y=190, width=80, height=20)

entryshiyanmingcheng= tkinter.Entry(root, width=150,textvariable=shiyanmingcheng)
entryshiyanmingcheng.place(x=110, y=190, width=150, height=20)

labelfenzurenshu = tkinter.Label(root, text='分组人数：')
labelfenzurenshu['background']='#FFFFFF'

labelfenzurenshu.place(x=20, y=220, width=80, height=20)
entryfenzurenshu= tkinter.Entry(root, width=150,textvariable=fenzurenshu)
entryfenzurenshu.place(x=110, y=220, width=150, height=20)


##====================================界面布局代码============================##









## =====================导入学生信息功能代码开始==============================##
def buttonImportXueshengXinxiClick():

    filename = tkinter.filedialog.askopenfilename(title='请选择Excel文件',
                                                  filetypes=[('Excel Files', '*.xlsx')
                                                             ] )
    if filename:

        excel = load_workbook(filename)
        # 获取sheet：
        sheet1 = excel.active  # 通过表名获取
        # 获取行数和列数：
        cols = sheet1.max_column  # 获取列数
        # Excel文件必须包含4列，分别为学号、姓名、专业年级、课程名称
        row = list(sheet1.rows)[0]
        if cols != 4 or row[0].value==None or row[3].value == None  :
            tkinter.messagebox.showerror(title='很抱歉', message='Excel文件格式不对')
            return

        # 遍历Excel文件每一行
        for rowIndex in range(1, sheet1.max_row):
            row = list(sheet1.rows)[rowIndex]
            if row[0].value:
                sql = "insert into students(xuehao,xingming,zhuanye,kecheng) values('"\
                             +str(row[0].value)+"','"+str(row[1].value)+"','"\
                             +str(row[2].value)+"','"+str(row[3].value)+"')"
                Common.doSQL(sql)
            else:
                pass
        tkinter.messagebox.showinfo(title='恭喜', message='导入成功')
buttonImportXueshengXinxi = tkinter.Button(root, text='导入学生信息', font=fonttype, background=backtype,
                                                   command=buttonImportXueshengXinxiClick)



## =====================导入学生信息功能代码结束==============================##




## =====================查看学生信息功能代码开始==============================##
# 查看学生信息
class windowChakanXueshengXinxi:
    def __init__(self, root, myTitle):
        self.top = tkinter.Toplevel(root, width=340, height=350)
        self.top.resizable(False, False)
        self.top.title(myTitle)
        self.top.attributes('-topmost', 1)

        # 用组合框来显示学生专业,调用通用功能类中的方法获取学生专业
        xueshengZhuanye = Common.getZhuanye()
        comboboxZhuanye = tkinter.ttk.Combobox(self.top, values=xueshengZhuanye)
        comboboxZhuanye.place(x=20, y=20, height=20, width=100)

        def buttonChakanClick():
            zhuanye = comboboxZhuanye.get()
            if not zhuanye:
                tkinter.messagebox.showerror(title='很抱歉', message='请选择一个专业')
                return

            # 根据选择的专业，获取该专业所有学生名单，格式为“学号,姓名”
            temp = Common.getXuehaoXingming(zhuanye)
            # 删除原有的所有行
            for row in treeXueshengMingdan.get_children():
                treeXueshengMingdan.delete(row)

            for iii, student in enumerate(temp):
                # 分割学号和姓名
                treeXueshengMingdan.insert('', iii, values=student.split(','))

        buttonChakan = tkinter.Button(self.top, text='查看', command=buttonChakanClick)
        buttonChakan.place(x=130, y=20, height=20, width=40)

        # 创建表格，设置表头,show="headings"用来隐藏树形控件的默认首列
        self.frame = tkinter.Frame(self.top)
        self.frame.place(x=20, y=50, width=270, height=280)
        # 垂直滚动条
        scrollBar = tkinter.Scrollbar(self.frame)
        scrollBar.pack(side=tkinter.RIGHT, fill=tkinter.Y)
        # 使用树形控件实现表格
        treeXueshengMingdan = tkinter.ttk.Treeview(self.frame,
                                                   columns=('col1', 'col2'),
                                                   show="headings",
                                                   yscrollcommand=scrollBar.set)
        treeXueshengMingdan.column('col1', width=140, anchor='center')
        treeXueshengMingdan.column('col2', width=110, anchor='center')
        treeXueshengMingdan.heading('col1', text='学号')
        treeXueshengMingdan.heading('col2', text='姓名')



        treeXueshengMingdan.pack(side=tkinter.LEFT, fill=tkinter.Y)



        # 树形控件与垂直滚动条结合
        scrollBar.config(command=treeXueshengMingdan.yview)

        def deletedata():
            sql='delete from students'
            a= tkinter.messagebox.askyesno( '提示','是否删除已有学生信息表')

            if a:
                Common.doSQL(sql)

                # 删除原有的所有行
                for row in treeXueshengMingdan.get_children():
                    treeXueshengMingdan.delete(row)

                xueshengZhuanye = Common.getZhuanye()
                comboboxZhuanye = tkinter.ttk.Combobox(self.top, values=xueshengZhuanye)
                comboboxZhuanye.place(x=20, y=20, height=20, width=100)

            else:
                return

        buttonshanchu = tkinter.Button(self.top, text='删除已有数据', command=deletedata)
        buttonshanchu.place(x=190, y=20, height=20, width=85)


def buttonChakanXueshengxinxiClick():

    buttonChakanXueshengxinxi['state'] = 'disabled'
    w1 = windowChakanXueshengXinxi(root, '查看学生信息')
    buttonChakanXueshengxinxi.wait_window(w1.top)
    buttonChakanXueshengxinxi['state'] = 'normal'


buttonChakanXueshengxinxi = tkinter.Button(root, text='查看学生信息', background=backtype, font=fonttype,command=buttonChakanXueshengxinxiClick)


## =====================查看学生信息功能代码结束==============================##

def buttonIPClick():
    # 获取并输出本机IP地址


    tkinter.messagebox.showinfo(title='本机IP地址', message=serverIP)


buttonIP = tkinter.Button(root, text='查看本机IP地址',background=backtype, font=fonttype, command=buttonIPClick)


## =====================在线点名功能代码开始==============================##
# 控制是否可以点名，1表示可以，0表示不可以
int_Dianming = tkinter.IntVar(root, value=0)

def thread_Dianming():

    # 开始监听
    global sockDianming
    sockDianming = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    sockDianming.bind(('', 30500))
    sockDianming.listen(200)
    while int_Dianming.get() == 1:
        try:
            # 接受一个客户端连接
            conn, addr = sockDianming.accept()
        except:
            continue
        data = conn.recv(1024).decode()
        try:
            # 客户端发来的消息格式为：学号,姓名,MAC地址
            xuehao, xingming, mac = data.split(',')
        except:
            conn.sendall('notmatch'.encode())
            conn.close()
            continue
        # 防SQL注入
        xuehao = re.sub(r'[;"\'=]', '', xuehao)
        xingming = re.sub(r'[;"\'=]', '', xingming)
        # 首先检查学号与姓名是否匹配，并且与数据库中的学生信息一致
        sqlIfMatch = "select count(xuehao) from students where xuehao='" + xuehao + "' and xingming='" + xingming + "'"
        if Common.getDataBySQL(sqlIfMatch)[0][0] != 1:
            conn.sendall('notmatch'.encode())
            conn.close()
        else:
            # 记录该学生点名信息：学号，姓名，时间，并反馈给客户端点名成功，然后客户端关闭连接
            currentTime = Common.getCurrentDateTime()
            # 获取一个半小时之前的时间
            startTime = Common.getStartDateTime()
            # 查看是否已经点名过，避免一个半小时内重复点名
            sqlShifouChongfuDianming = "select count(xuehao) from dianming where xuehao='" \
                                       + xuehao + "' and shijian >='" + startTime + "'"

            if Common.getDataBySQL(sqlShifouChongfuDianming)[0][0] != 0:
                conn.sendall('repeat'.encode())
                conn.close()
            else:
                a = entryshiyanmingcheng.get().strip()
                b = entryfenzurenshu.get().strip()

                if b == '':
                    b1 = 1                              # 判断实验名称和实验人数是否输入
                else:
                    b1 = int(b)


                # 检查是否代替点名，根据学生端IP地址识别

                sqlMacShifouChongfu = "select count(mac) from dianming where mac='" \
                                      + mac + "' and shijian>='" + startTime + "'"

                if  Common.getDataBySQL(sqlMacShifouChongfu)[0][0] >= b1:
                    conn.sendall('numberenough'.encode())
                    conn.close()
                else:


                    # 插入点名信息
                    sqlDianming = "insert into dianming(xuehao,shijian,mac,shiyanming,fenzurenshu) values('" \
                                 + xuehao + "','" + currentTime + "','" + mac + "','"+a+"','"+str(b1)+"')"
                    Common.doSQL(sqlDianming)
                    conn.sendall('ok'.encode())
                    conn.close()

                      #判断是否同一个实验的第二次点名

                    sqlshifouercishiyan="select count(xuehao) from dianming where shiyanming='"+a+"'    and xuehao='"+xuehao+"'"

                    if  Common.getDataBySQL(sqlshifouercishiyan)[0][0] ==1:                                #如果第一次点名
                        sqlshifoutongyitian="select shijian from dianming  where  shiyanming='"+a+"'   order by shijian asc limit 1"
                        shifoutongyitian=Common.getDataBySQL(sqlshifoutongyitian)[0][0]  #判断是否是第一天实验

                        str_p = shifoutongyitian
                        dateTime_p = datetime.datetime.strptime(str_p, '%Y-%m-%d %H:%M:%S')
                        time1= dateTime_p+ datetime.timedelta(days=1)

                        if Common.getCurrentDateTime()> (str)(time1):  #若果不是第一天实验，但是是第一次点名，则不干扰分组表数据
                            pass
                        else:  ##若果是第一天实验，则开始操作分组表数据

                            #插入分组表数据
                            sqlfenzu="select count(mac) from fenzubiao where shiyanming='"+a+"'  and  mac='"+mac+"' "
                            fenzushifoucunzai=Common.getDataBySQL(sqlfenzu)[0][0]           #查询是否已经有此分组
                            if fenzushifoucunzai == 0 :  ##若果无此分组，则创建一个分组，并插入分组数据
                                sqlcharufenzu="insert into fenzubiao(chengyuan,shiyanming,zhuanye,mac,defen,imagenum,filenum)  values((select xingming from students " \
                                              " where xuehao='"+xuehao+"' ),'"+entryshiyanmingcheng.get().strip()+"',(select zhuanye from students where " \
                                             "xuehao='"+xuehao+"'),'"+mac+"',0,0,0) "
                                Common.doSQL(sqlcharufenzu)

                            else :  ##若果有此分组，则追加数据
                                sqlshifoutongban="select zhuanye from fenzubiao where shiyanming='"+a+"'  and  mac='"+mac+"' "
                                shifoutongban=   Common.getDataBySQL(sqlshifoutongban)[0][0]
                                sqlshifoutongban2="select zhuanye from students where xuehao='"+xuehao+"'"
                                shifoutongban2=Common.getDataBySQL(sqlshifoutongban2)[0][0]

                                if shifoutongban==shifoutongban2 :  #若果追加的学生数据中班级和分组中已有班级相同，则直接追加分组成员信息
                                    sqladdfenzu = "UPDATE fenzubiao SET chengyuan=((select chengyuan from fenzubiao where mac='" + mac + "' and shiyanming='" + entryshiyanmingcheng.get().strip() + "' ) ||','||" \
                                                  "(select xingming from students where xuehao='" + xuehao + "')) WHERE  mac='" + mac + "' and shiyanming='" + entryshiyanmingcheng.get().strip() + "'"
                                    Common.doSQL(sqladdfenzu)

                                else :              ##如果班级不同，则不但要追加分组成员，还要追加分组班级
                                    shifoutongban=shifoutongban+','+shifoutongban2
                                    sqladdfenzu = "UPDATE fenzubiao SET chengyuan=((select chengyuan from fenzubiao where mac='" + mac + "' and shiyanming='" + entryshiyanmingcheng.get().strip() + "' ) ||','||" \
                                          "(select xingming from students where xuehao='" + xuehao + "')),zhuanye='"+shifoutongban+"' WHERE  mac='" + mac + "' and" \
                                             " shiyanming='" + entryshiyanmingcheng.get().strip() + "'"
                                    Common.doSQL(sqladdfenzu)

                    else:                   ## 如果不是第一次点名那就不操作分组表
                        pass


    sockDianming.close()



# 开始点名
def buttonStartDianmingClick():

    if entryshiyanmingcheng.get().strip() == '':
        tkinter.messagebox.showerror('抱歉', '请输入实验名称')
        return
    if int_Dianming.get() == 1:
        tkinter.messagebox.showerror('很抱歉', '现在正在点名')
        return
    tkinter.messagebox.showinfo('恭喜', '设置成功，现在开始点名')
    # 开始点名
    int_Dianming.set(1)

    t = threading.Thread(target=thread_Dianming)
    t.start()



buttonStartDianming = tkinter.Button(root, text='开始点名', font=fonttype,background=backtype, command=buttonStartDianmingClick)
buttonStartDianming.place(x=20, y=20, height=30, width=100)

def buttonStopDianmingClick():

    if int_Dianming.get() == 0:
        tkinter.messagebox.showerror('很抱歉', '还没开始点名')
        return

    # 停止点名
    int_Dianming.set(0)
    sockDianming.close()
    time.sleep(0.1)

    try:
        sql = 'select kecheng from students where xuehao=(select xuehao from dianming where shijian<="' \
          + Common.getCurrentDateTime() + '"  order by shijian desc limit 1)'
        currentZhuanye = Common.getDataBySQL(sql)[0][0]
        sql = 'select count(kecheng) from students where kecheng="' + currentZhuanye + '"'
        totalRenshu = Common.getDataBySQL(sql)[0][0]

        sql = 'select count(xuehao) from dianming where shijian<="' + Common.getCurrentDateTime() \
          + '" and shijian>="' + Common.getStartDateTime() + '"'
        totalShidao = Common.getDataBySQL(sql)[0][0]
    except:
        currentZhuanye='无'
        totalRenshu='0'
        totalShidao='0'






    message = '设置成功，现在停止点名!\n当前点名专业：' + currentZhuanye \
              + '\n应到人数：' + str(totalRenshu) + '\n实到人数：' + str(totalShidao)
    tkinter.messagebox.showinfo('恭喜', message)


buttonStopDianming = tkinter.Button(root, text='结束点名', font=fonttype, background=backtype,command=buttonStopDianmingClick)
buttonStopDianming.place(x=130, y=20, height=30, width=100)
## ====================================在线点名功能代码结束=========================================##




## ====================================在线答疑功能代码开始=========================================##

int_xueshengTiwen = tkinter.IntVar(root, value=0)


class windowXueshengTiwen:
    def __init__(self, root, conn, data):
        # 创建面板容器，用于放置其他控件
        self.top = tkinter.Toplevel(root, width=300, height=220)
        self.top.resizable(False, False)
        tttt = tkinter.StringVar(self.top)
        xuehaoXingming, message = data.split(':')
        xuehao = xuehaoXingming.split(',')[0]
        tttt.set(message)
        self.top.title('来自' + xuehaoXingming + ' 的消息')

        # 把本次学生提问记录添加到数据库
        sqlTiwen = "insert into xueshengtiwen(xuehao,wenti,shijian) values('" \
                   + xuehao + "','" + message + "','" + Common.getCurrentDateTime() + "')"
        Common.doSQL(sqlTiwen)
        self.top.attributes('-topmost', 1)

        # 把接收到的消息放到弹出窗口的文本框中
        entryMessage = tkinter.scrolledtext.ScrolledText(self.top, wrap=tkinter.WORD)
        entryMessage.insert(tkinter.INSERT, message)
        entryMessage.place(x=20, y=20, width=250, height=60)

        # 定义回复消息使用的文本框控件
        entryHuifu = tkinter.scrolledtext.ScrolledText(self.top, wrap=tkinter.WORD)
        entryHuifu.place(x=20, y=90, width=250, height=60)

        def buttonHuifuClick():
            # 回复，解答
            huifu = entryHuifu.get(0.0, tkinter.END)
            conn.sendall(huifu.encode())
            conn.close()
            # 把本次回复记录添加到数据库
            sqlTiwen = "insert into xueshengtiwen(xuehao,wenti,shijian) values('" \
                       + xuehao + "','老师回复 " + xuehaoXingming + ":" + huifu \
                       + "','" + Common.getCurrentDateTime() + "')"
            Common.doSQL(sqlTiwen)
            # 关闭窗口
            self.top.destroy()

        buttonHuifu = tkinter.Button(self.top, text='回复解答', command=buttonHuifuClick)
        buttonHuifu.place(x=40, y=160, width=80, height=20)

        def buttonTongyiJiangjieClick():
            # 不回复该问题，稍后统一讲解给全班同学
            conn.sendall(b'wait')
            conn.close()
            # 把本次回复记录添加到数据库
            sqlTiwen = "insert into xueshengtiwen(xuehao,wenti,shijian) values('" \
                       + xuehao + "','老师回复 " + xuehaoXingming + ":该问题稍后统一讲解！','" \
                       + Common.getCurrentDateTime() + "')"
            Common.doSQL(sqlTiwen)
            # 关闭窗口
            self.top.destroy()

        buttonTongyiJiangjie = tkinter.Button(self.top, text='稍后统一讲解', command=buttonTongyiJiangjieClick)
        buttonTongyiJiangjie.place(x=130, y=160, width=80, height=20)

        # 老师没有回答，直接关闭窗口
        def closeTiwenWindow():
            conn.sendall('close'.encode())
            conn.close()
            self.top.destroy()

        self.top.protocol('WM_DELETE_WINDOW', closeTiwenWindow)

def thread_xueshengTiwenMain():
    global sockTiwen
    sockTiwen = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    # 监听20000端口
    sockTiwen.bind(('', 20000))
    # 最多允许同时10个学生提问
    sockTiwen.listen(10)
    while int_xueshengTiwen.get() == 1:
        try:
            # 接收一个连接
            conn, addr = sockTiwen.accept()
        except:
            return
        # 接收消息，消息内容为“学号,姓名:问题内容”
        data = conn.recv(1024).decode()
        if data == 'over':
            conn.close()
            continue
        xuehaoXingming, message = data.split(':')
        xuehao, xingming = xuehaoXingming.split(',')
        xuehao = xuehao.replace(';', '').replace('"', '').replace("'", '').replace('=', '')
        xingming = xingming.replace(';', '').replace('"', '').replace("'", '').replace('=', '')
        sql = "select count(xuehao) from students where xuehao='" + xuehao.strip() + "' and xingming='" + xingming.strip() + "'"
        t = Common.getDataBySQL(sql)[0][0]
        if t != 1:
            conn.sendall('notmatch'.encode())
            conn.close()
        else:
            w = windowXueshengTiwen(root, conn, data)
    sockTiwen.close()


# 启动接收学生提问的线程
def buttonJieshouTiwenClick():


    if int_xueshengTiwen.get() == 1:
        tkinter.messagebox.showerror('很抱歉', '现在正在接收提问，不用重复设置')
        return

    int_xueshengTiwen.set(1)
    tkinter.messagebox.showinfo('恭喜', '设置成功，现在开始接收学生提问！')
    t_tiwen = threading.Thread(target=thread_xueshengTiwenMain)
    t_tiwen.start()


buttonJieshouTiwen = tkinter.Button(root, text='开始接收提问',background=backtype, font=fonttype, command=buttonJieshouTiwenClick)
buttonJieshouTiwen.place(x=20, y=100, height=30, width=100)


# 停止接收学生提问的线程
def buttonTingzhiTiwenClick():

    if int_xueshengTiwen.get() == 0:
        tkinter.messagebox.showerror('很抱歉', '还没开始接收学生提问！')
        return

    int_xueshengTiwen.set(0)
    sockTiwen.close()
    time.sleep(0.1)
    tkinter.messagebox.showinfo('恭喜', '设置成功，现在停止接收学生提问！')


buttonTingzhiTiwen = tkinter.Button(root, text='停止接收提问', background=backtype,font=fonttype, command=buttonTingzhiTiwenClick)
buttonTingzhiTiwen.place(x=130, y=100, height=30, width=100)
## =====================================在线答疑功能代码结束==============================##



##-=====================================随机提问功能代码==================================##

class suijitiwenwindows:
    def __init__(self, root, myTitle):
        self.top = tkinter.Toplevel(root, width=350, height=200)
        self.top.resizable(False, False)
        self.top.title(myTitle)
        self.top.attributes('-topmost', 1)

        # 用组合框来显示学生专业,调用通用功能类中的方法获取学生专业
        xueshengZhuanye = Common.getZhuanye()
        labelzhuanye = tkinter.Label(self.top, text='专业：')
        labelzhuanye.place(x=20, y=20, width=60, height=20)

        comboboxZhuanye = tkinter.ttk.Combobox(self.top, values=xueshengZhuanye)
        comboboxZhuanye.place(x=90, y=20, width=80, height=20)

        xueshengxingming = tkinter.StringVar(self.top)
        xueshengxingming.set('')
        xuehao = tkinter.StringVar(self.top)
        xuehao.set('')
        labelxuehao = tkinter.Label(self.top, text='学号：')
        labelxuehao.place(x=20, y=50, width=60, height=20)

        entryxuehao = tkinter.Entry(self.top, width=150, textvariable=xuehao)
        entryxuehao.place(x=90, y=50, width=100, height=20)

        labelxingming = tkinter.Label(self.top, text='姓名：')
        labelxingming.place(x=20, y=80, width=60, height=20)

        entryxingming = tkinter.Entry(self.top, width=100, textvariable=xueshengxingming)
        entryxingming.place(x=90, y=80, width=100, height=20)


        def buttonchouquClick():
            zhuanye = comboboxZhuanye.get()
            if not zhuanye:
                tkinter.messagebox.showerror(title='提示', message='请选择一个专业')
                return

            # 根据选择的专业，获取该专业所有学生名单，格式为“学号,姓名”
            temp = Common.getXuehaoXingming(zhuanye)
            #生成随机数

            c = random.randint( 0 , len(temp)-1)
            #输出随机数对应的数列元素
            xuehao1 ,xueshengxingming1=temp[c].split(",")

            xuehao.set(xuehao1)
            xueshengxingming.set(xueshengxingming1)
            sqlchengyuan = "select chengyuan,zhuanye from fenzubiao where shiyanming=='" + entryshiyanmingcheng.get() + "'"
            chengyuan = Common.getDataBySQL(sqlchengyuan)

            temp1 = ''
            for item in chengyuan:

                if (xueshengxingming1.strip() in item[0]) and (zhuanye in item[1]):  # 返回的temp是元组所以使用
                    temp1 = item[0]
                    break

            if not temp1:
                temp1=xueshengxingming1


            shifoudianming = "select count(xuehao) from dianming where  shijian >= '"+Common.getStartDateTime() +"' and  xuehao='"+xuehao1+"' "
            t = Common.getDataBySQL(shifoudianming)[0][0]
            if t==0:
                tkinter.messagebox.showinfo('报告','点名表中，此人今天没点名！'+str(t)+Common.getStartDateTime())
                return
            try:
                sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
                sock.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
                temp1=temp1+'0'+xueshengxingming1
                sock.sendto(temp1.encode(), (IP, 10000))
            except:
                pass
        buttonchouqu = tkinter.Button(self.top, text='抽取幸运儿', command=buttonchouquClick)
        buttonchouqu.place(x=190, y=20, height=20, width=80)



def buttonsuijitiwenClick() :
    if entryshiyanmingcheng.get()=='':
        tkinter.messagebox.showinfo("抱歉",'请输入实验名称')
        return

    buttonsuijitiwen['state'] = 'disabled'
    w2 = suijitiwenwindows(root, '随机提问')
    buttonsuijitiwen.wait_window(w2.top)
    buttonsuijitiwen['state'] = 'normal'


buttonsuijitiwen = tkinter.Button(root,text='随机提问',background=backtype, font=fonttype,command=buttonsuijitiwenClick)
buttonsuijitiwen.place(x=240, y=20, height=30, width=100)

##====================================随机提问功能代码结束=========================##



##=================================收截图功能代码=================================##

# 0停止收作业，1开始收作业
int_zuoye = tkinter.IntVar(root, value=0)


def thread_ShouZuoye(conn, today):
    # 最终把客户端发来的截图保存为“学号，姓名.jpg”
    # 开始接收客户端发来的截图
    BUFSIZE = 1024
    FILEINFO_SIZE = struct.calcsize('I128sI')

    fhead = conn.recv(FILEINFO_SIZE)
    filenamelength, filename, filesize = struct.unpack('I128sI', fhead)
    filename = filename.decode()
    filename = filename[:filenamelength]
    ttt = filename.split('.')[0]

    # 如果学号和姓名不匹配，拒绝接收作业图片
    xuehao, xingming = ttt.split('_')
    xuehao = xuehao.replace(';', '').replace('"', '').replace("'", '').replace('=', '')
    xingming = xingming.replace(';', '').replace('"', '').replace("'", '').replace('=', '')
    sql = "select count(xuehao) from students where xuehao='" + xuehao.strip() + "' and xingming='" + xingming.strip() + "'"
    t = Common.getDataBySQL(sql)[0][0]
    if t != 1:
        conn.sendall('notmatch'.encode())
        conn.close()
        return
    else:
        conn.sendall('ok'.encode())
    filename = filename[:-4] + '_'.join(Common.getCurrentDateTime().split()) + filename[-4:]
    filename = filename.replace('-', '_')
    filename = filename.replace(':', '_')
    if not os.path.exists(today + '\\' +ttt):
        os.mkdir(os.path.join(today, ttt))
          #增加一个today下的文件夹，以学生姓名学号为名
    wenjianxuhao=1
    filename = today + '\\' +ttt+'\\'+str(wenjianxuhao)+'.png'

    # 判断文件夹下该文件名是否已经有了，有的话给文件序号加1
    for f in os.listdir(today + '\\' +ttt):
        if f.startswith(str(wenjianxuhao)):
            wenjianxuhao= wenjianxuhao+1
            filename = today + '\\' + ttt + '\\' + str(wenjianxuhao)+'.png'

    fp = open(filename, 'wb')
    restsize = filesize
    while True:
        if restsize > BUFSIZE:
            filedata = conn.recv(BUFSIZE)
        else:
            filedata = conn.recv(restsize)
        if not filedata:
            break
        fp.write(filedata)
        restsize = restsize - len(filedata)
        if restsize == 0:
            break
    fp.close()
    sqlchengyuan = "select chengyuan ,zhuanye from fenzubiao where shiyanming=='" + entryshiyanmingcheng.get() + "'"
    chengyuan = Common.getDataBySQL(sqlchengyuan)
    sqlzhuanye = "select zhuanye from students where xuehao=='" + xuehao + "'"
    zhuanye = Common.getDataBySQL(sqlzhuanye)[0][0]
    temp1 = ''
    for item in chengyuan:
        if (xingming.strip() in item[0]) and (zhuanye in item[1]):  # 返回的temp是元组所以使用
            temp1 = item[0]
            break

    if not temp1:
        tkinter.messagebox.showinfo('提示', '请通过点名加入实验分组')
    else:

        sqlinsertfilenum = "update fenzubiao set imagenum='"+str(wenjianxuhao)+"' where shiyanming='" + entryshiyanmingcheng.get() + "' and chengyuan='" + \
                           item[0] + "' "
        Common.doSQL(sqlinsertfilenum)
    conn.close()


def thread_ZuoyeMain():
    today = Common.getCurrentDateTime().split()[0]  # yyyy-mm-dd hh:mm:ss
    if not os.path.exists(today):
        os.mkdir(today)
    # 开始监听
    global sockShouzuoye
    sockShouzuoye = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    sockShouzuoye.bind(('', 30100))
    sockShouzuoye.listen(200)
    while int_zuoye.get() == 1:
        time.sleep(0.05)
        try:
            conn, addr = sockShouzuoye.accept()
        except:
            return
        t = threading.Thread(target=thread_ShouZuoye, args=(conn, today))
        t.start()
    sockShouzuoye.close()


def buttonStartShouzuoyeClick():

    if entryshiyanmingcheng.get().strip() == '':
        tkinter.messagebox.showerror('抱歉', '请输入实验名称')
        return

    if int_zuoye.get() == 1:
        tkinter.messagebox.showerror('很抱歉', '现在正在收作业，不用重复设置！')
        return
    int_zuoye.set(1)
    tkinter.messagebox.showinfo('恭喜', '设置成功，现在开始收作业')
    t = threading.Thread(target=thread_ZuoyeMain)
    # 启动收作业线程
    t.start()


buttonStartShouzuoye = tkinter.Button(root, text='开始收截图作业',background=backtype, font=fonttype, command=buttonStartShouzuoyeClick)
buttonStartShouzuoye.place(x=20, y=140, height=30, width=100)


def buttonStopShouzuoyeClick():


    if int_zuoye.get() == 0:
        tkinter.messagebox.showerror('很抱歉', '还没开始收作业')
        return
    int_zuoye.set(0)
    sockShouzuoye.close()
    time.sleep(0.1)
    tkinter.messagebox.showinfo('恭喜', '设置成功，现在已停止收作业')


buttonStopShouzuoye = tkinter.Button(root, text='结束收截图作业', background=backtype, font=fonttype,command=buttonStopShouzuoyeClick)
buttonStopShouzuoye.place(x=130, y=140, height=30, width=100)
## ========================收截图功能代码结束====================================##


## =====================在线上传文件交作业功能代码开始===========================##
int_wenjianZuoye = tkinter.IntVar(root, value=0)  # 负责控制是否允许接收学生上传文件作业的变量


# 负责具体收文件作业的线程函数
def thread_ShouWenjianZuoye(conn, today):
    BUFSIZE = 1024
    FILEINFO_SIZE = struct.calcsize('I128sI')
    fhead = conn.recv(FILEINFO_SIZE)
    filenamelength, filename, filesize = struct.unpack('I128sI', fhead)
    filename = filename.decode()
    filename = filename[:filenamelength]

    ttt = filename.split('_1_')[0]
    # 如果学号和姓名不匹配，拒绝接收作业
    xuehao, xingming = ttt.split('_')
    xuehao = re.sub('''[:"'=]''', '', xuehao)
    xingming = re.sub('''[:"'=]''', '', xingming)
    sql = "select count(xuehao) from students where xuehao='" + xuehao.strip() + "' and xingming='" + xingming.strip() + "'"
    t = Common.getDataBySQL(sql)[0][0]
    if t != 1:
        conn.sendall('notmatch'.encode())
        conn.close()
        return
    else:
        conn.sendall('ok'.encode())
    index = filename.rindex('.')
    filename = '工程文件'+ filename[index:]



    # 增加一个today下的文件夹，以学生姓名学号为名
    if not os.path.exists(today + '\\' + ttt):
        os.mkdir(os.path.join(today, ttt))

    filename1 = today + '\\' + ttt + '\\' + filename

    # # 首先删除本次作业期间之前上交的作业，只保留最后一次的作业
    for f in os.listdir(today + '\\' + ttt):
        if f.startswith(filename):
            os.remove(filename1)



    fp = open(filename1, 'wb')
    restsize = filesize
    while True:
        if restsize > BUFSIZE:
            filedata = conn.recv(BUFSIZE)
        else:
            filedata = conn.recv(restsize)
        if not filedata:
            break
        fp.write(filedata)
        restsize = restsize - len(filedata)
        if restsize == 0:
            break
    fp.close()
    sqlchengyuan="select chengyuan,zhuanye from fenzubiao where shiyanming=='"+entryshiyanmingcheng.get()+"'"
    chengyuan =  Common.getDataBySQL(sqlchengyuan)
    sqlzhuanye="select zhuanye from students where xuehao=='"+xuehao+"'"
    zhuanye=Common.getDataBySQL(sqlzhuanye)[0][0]
    temp1=''
    for item in  chengyuan:

        if (xingming.strip() in item[0]) and (zhuanye in item[1]) :            #返回的temp是元组所以使用
            temp1=item[0]
            break

    if not temp1  :
        tkinter.messagebox.showinfo('提示',xingming.strip()+'未加入分组！')
    else :

        sqlinsertfilenum = "update fenzubiao set filenum='已提交' where shiyanming='"+entryshiyanmingcheng.get()+"' and chengyuan='"+item[0]+"' "
        Common.doSQL(sqlinsertfilenum)

    conn.close()


# 负责监听端口的收文件作业主线程函数
def thread_wenjianZuoyeMain():
    today = Common.getCurrentDateTime().split()[0]
    if not os.path.exists(today):
        os.mkdir(today)

    # 开始监听
    global sockShouWenjianZuoye
    sockShouWenjianZuoye = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    sockShouWenjianZuoye.bind(('', 10500))
    sockShouWenjianZuoye.listen(200)
    while int_wenjianZuoye.get() == 1:
        time.sleep(0.05)
        try:
            conn, addr = sockShouWenjianZuoye.accept()
        except:
            return
        t = threading.Thread(target=thread_ShouWenjianZuoye, args=(conn, today))
        t.start()
    sockShouWenjianZuoye.close()


# 开始接收学生上传Python程序文件作业
def buttonStartJieshouWenjianZuoyeClick():
    if entryshiyanmingcheng.get().strip() == '':
        tkinter.messagebox.showerror('抱歉', '请输入实验名称')
        return
    if int_wenjianZuoye.get() == 1:
        tkinter.messagebox.showerror('很抱歉', '现在正在收作业，不用重复设置！')
        return
    int_wenjianZuoye.set(1)
    tkinter.messagebox.showinfo('恭喜', '设置成功，现在开始收作业')
    t = threading.Thread(target=thread_wenjianZuoyeMain)
    # 启动收作业线程
    t.start()


buttonStartJieshouWenjianZuoye = tkinter.Button(root,
                                                text='开始收文件作业', font=fonttype,background=backtype,
                                                command=buttonStartJieshouWenjianZuoyeClick)
buttonStartJieshouWenjianZuoye.place(x=20, y=60, height=30, width=100)


# 结束接收学生上传Python程序文件作业
def buttonStopJieshouWenjianZuoyeClick():

    if int_wenjianZuoye.get() == 0:
        tkinter.messagebox.showerror('很抱歉', '还没开始收作业')
        return
    int_wenjianZuoye.set(0)
    sockShouWenjianZuoye.close()
    time.sleep(0.1)
    tkinter.messagebox.showinfo('恭喜', '设置成功，现在已停止收作业')


buttonStopJieshouWenjianZuoye = tkinter.Button(root,
                                               text='结束收文件作业', font=fonttype,background=backtype,
                                               command=buttonStopJieshouWenjianZuoyeClick)
buttonStopJieshouWenjianZuoye.place(x=130, y=60, height=30, width=100)

## ====================================在线上传文件交作业功能代码结束======================================##




##=========================================上传文件说明代码================================================##

int_shuomingwenjian = tkinter.IntVar(root, value=0)

def chuanwenjian_thread():


    PORT = 8880
    Handler = http.server.SimpleHTTPRequestHandler  #定义对链接过来的请求如何处理
    global httpd

    httpd= socketserver.TCPServer(("", PORT), Handler)  # TCPServer 支持ipv4的TCP协议的服务器
    httpd.serve_forever()  #持续循环运行







def  ShuomingwenjianOnclick():

    if int_shuomingwenjian.get() == 1:
        tkinter.messagebox.showinfo('提示', '已经设置，不用重复设置！')
        return

    tkinter.messagebox.showinfo('提示','开启文件共享成功！')
    t = threading.Thread(target=chuanwenjian_thread)
    t.start()
    int_shuomingwenjian.set(1)



buttonShuomingWenjian = tkinter.Button(root,text = '上传说明文件',background=backtype, font=fonttype,command = ShuomingwenjianOnclick)
buttonShuomingWenjian.place(x=240, y=60, height=30, width=100)



##=====================================上传说明文件代码结束===================================================##




## =====================================数据导出功能代码开始==================================================##
# 数据导出到xlsx文件
def buttonDaochuClick():
    try:
        import openpyxl
        from openpyxl import Workbook
    except:
        tkinter.messagebox.showerror('抱歉', '您需要安装openpyxl扩展库')
        return

    # 设置要导出数据的时间段


    startTime = tkinter.simpledialog.askstring('请输入开始日期',
                                                   '开始日期：',
                                                   initialvalue='2020-3-1')

    if startTime == None :
        return
    try:
        import datetime
        # 确保输入的是正确日期格式
        y, m, d = map(int, startTime.split('-'))
        startTime = str(datetime.date(y, m, d))
    except:
        tkinter.messagebox.showerror('警告','输入格式错误，请重新输入！')
        buttonDaochuClick()




    endTime = tkinter.simpledialog.askstring('请输入结束日期',
                                                 '结束日期：',
                                                 initialvalue='2020-3-31')
    if  endTime==None:
        return
    try:
        import datetime
        # 确保输入的是正确日期格式
        y, m, d = map(int, endTime.split('-'))
        endTime = str(datetime.date(y, m, d))
    except:
        tkinter.messagebox.showerror('警告', '输入格式错误，请重新输入！')
        buttonDaochuClick()

    shiyanming  = tkinter.simpledialog.askstring('请输入要导出的实验名称',
                                               '实验名称：',
                                               initialvalue='全部导出')
    # 统一的时间段条件


    shijianDuan = ' shijian>="' + startTime + '" and shijian<="' + endTime + '"'

    wb = Workbook()
    # 删除默认的worksheet
    wb.remove_sheet(wb.worksheets[0])
    # 点名记录
    ws = wb.create_sheet(title='在线点名情况')
    ws.append(['学号', '姓名', '点名时间','实验名称','班级'])
    sql = 'select students.xuehao, students.xingming, shijian,shiyanming ,students.zhuanye from students, dianming' \
          + ' where students.xuehao=dianming.xuehao and ' + shijianDuan \
          + ' order by students.xuehao, shijian'
    data = Common.getDataBySQL(sql)
    for d in data:
        ws.append(list(d))

    # 提问情况
    ws = wb.create_sheet(title='提问情况')
    ws.append(['学号', '姓名', '提问时间', '问题','班级'])
    sql = 'select students.xuehao, students.xingming, shijian, wenti,students.zhuanye from students, xueshengtiwen' \
          + ' where students.xuehao=xueshengtiwen.xuehao and ' + shijianDuan \
          + ' order by students.xuehao'
    data = Common.getDataBySQL(sql)
    for d in data:
        ws.append(list(d))

    # 分组情况
    ws = wb.create_sheet(title='分组情况')
    ws.append(['成员', '实验名', '专业', '得分','文件是否提交','提交截图数量'])
    if shiyanming =='全部导出':
        sql = "select chengyuan,shiyanming ,zhuanye,defen ,filenum,imagenum from fenzubiao  order by shiyanming"
    else :
        sql = "select chengyuan,shiyanming ,zhuanye,defen ,filenum,imagenum from fenzubiao where shiyanming='"+shiyanming+"' order by shiyanming"
    data = Common.getDataBySQL(sql)
    for d in data:
        ws.append(list(d))



    wb.save('导出的表文件.xlsx')
    tkinter.messagebox.showinfo('恭喜', '数据导出成功，请查看系统文件夹中的"导出的表文件.xlsx"文件')


buttonDaochu = tkinter.Button(root, text='数据导出',background=backtype, font=fonttype, command=buttonDaochuClick)

## =============================================数据导出功能代码结束================================##


# ===========================================合并数据库功能代码开始================================##
def buttonMergeDatabaseClick():
    filename = tkinter.filedialog.askopenfilename(title='请选择SQLite数据库文件',
                                                  filetypes=[('sqlite Files', '*.db')])
    if not filename:
        return
    if not filename.endswith('.db'):
        tkinter.messagebox.showerror('抱歉', '您必须选择sqlite数据库文件')
        return
    if os.path.abspath(filename) == os.getcwd() + '\\' + 'database.db':
        tkinter.messagebox.showerror('抱歉', '您不能合并数据库自身')
        return

    # 开始合并数据库
    srcConn = sqlite3.connect(filename)
    dstConn = sqlite3.connect('database.db')
    srcCur = srcConn.cursor()
    dstCur = dstConn.cursor()



    # 合并点名数据表
    srcCur.execute('select mac,xuehao,shijian ,shiyanming,fenzurenshu  from dianming')
    dstCur.execute('select mac,xuehao,shijian,shiyanming,fenzurenshu  from dianming')
    dstRecords = dstCur.fetchall()
    for record in srcCur.fetchall():
        if record not in dstRecords:
            try:
                sqlMerge = 'insert into dianming(mac,xuehao,shijian,shiyanming,fenzurenshu ) values("' + \
                           record[0] + '","' + record[1] + '","' + record[2] + '","' + record[3] + '","' + record[4] + '")'
                dstCur.execute(sqlMerge)
            except:
                tkinter.messagebox.showinfo('警告',record)
                return

    dstConn.commit()

    # 合并学生提问数据表
    srcCur.execute('select xuehao,shijian ,wenti  from xueshengtiwen')
    dstCur.execute('select xuehao,shijian ,wenti  from xueshengtiwen')
    dstRecords = dstCur.fetchall()
    for record in srcCur.fetchall():
        if record not in dstRecords:
            try:
                sqlMerge = 'insert into xueshengtiwen(xuehao,shijian,wenti ) values("' + \
                           record[0] + '","' + record[1] + '","' + record[2] + '")'
                dstCur.execute(sqlMerge)
            except:
                tkinter.messagebox.showinfo('警告', record)
                return
    dstConn.commit()


    # 合并分组数据表
    srcCur.execute('select chengyuan,shiyanming ,defen,zhuanye ,mac,imagenum,filenum from fenzubiao')
    dstCur.execute('select chengyuan,shiyanming ,defen,zhuanye,mac,imagenum,filenum from fenzubiao')
    dstRecords = dstCur.fetchall()


    for record in srcCur.fetchall():
        if record not in dstRecords:
            try:

                sqlMerge = 'insert into fenzubiao(chengyuan ,shiyanming ,defen,zhuanye ,mac,imagenum,filenum ) values("' + \
                           record[0] + '","' + record[1] + '","' + record[2] + '","' + record[3] + '","' + record[4] + '","' + record[5] + '","' + record[6] + '")'
                dstCur.execute(sqlMerge)
            except:
                tkinter.messagebox.showinfo('警告', record)
                return

    dstConn.commit()





    tkinter.messagebox.showinfo('恭喜', '数据库合并成功！')


buttonMergeDatabase = tkinter.Button(root, text='合并数据库', background=backtype,font=fonttype, command=buttonMergeDatabaseClick)

## =================================合并数据库功能代码结束==============================##



##===============================数据查看功能代码=======================================##

# 点名情况查看    学号，姓名， 点名时间，通过实验名，班级筛选

int_windowchakan = tkinter.IntVar(root, value=0)


class windowChuQinchakan:
    def __init__(self, root, myTitle):
        self.top = tkinter.Toplevel(root, width=450, height=380)
        self.top.resizable(False, False)
        self.top.title(myTitle)
        self.top.attributes('-topmost', 1)
        # 组合框选择专业，或文本框查看特定学号学生的提问情况

        labelzhuanye = tkinter.Label(self.top, text='专业：')
        labelzhuanye.place(x=20, y=20, width=80, height=20)
        labelshiyan = tkinter.Label(self.top, text='实验名：')
        labelshiyan.place(x=20, y=50, width=80, height=20)

        xueshengZhuanye = Common.getZhuanye()
        comboboxZhuanye = tkinter.ttk.Combobox(self.top, values=xueshengZhuanye)
        comboboxZhuanye.place(x=90, y=20, height=20, width=120)
        xueshengShiyan = Common.getShiyan()
        comboboxShiyan = tkinter.ttk.Combobox(self.top, values=xueshengShiyan)
        comboboxShiyan.place(x=90, y=50, height=20, width=120)





        # 查看指定专业所有同学的提问情况
        def chakanchuqin():
            zhuanye = comboboxZhuanye.get()
            shiyanming=comboboxShiyan.get()
            if not zhuanye or (not shiyanming ):
                tkinter.messagebox.showerror('很抱歉', '请选择专业和实验名')
                return
            else:
                conn = sqlite3.connect('database.db')
                cur = conn.cursor()
                cur.execute(
                    "select students.xuehao,students.xingming,dianming.shijian from students,dianming  where students.xuehao=dianming.xuehao and students.zhuanye='" + zhuanye +
                    "' and  dianming.shiyanming='"+shiyanming+"' order by students.xuehao, dianming.shijian desc")   ##desc降序排列asc升序排列
                temp = cur.fetchall()
                conn.close
                # 删除原有的所有行
                for row in treeXueshengMingdan.get_children():
                    treeXueshengMingdan.delete(row)

                for iii, student in enumerate(temp):
                    treeXueshengMingdan.insert('', iii, values=(student[0], student[1], student[2]))

        buttonZhuanye = tkinter.Button(self.top, text='出勤记录查看', command=chakanchuqin)
        buttonZhuanye.place(x=240, y=20, height=20, width=100)


        # 创建表格，设置表头,show="headings"用来隐藏树形控件的默认首列
        self.frame = tkinter.Frame(self.top)
        self.frame.place(x=10, y=80, width=440, height=280)
        # 垂直滚动条
        scrollBar = tkinter.Scrollbar(self.frame)
        scrollBar.pack(side=tkinter.RIGHT, fill=tkinter.Y)
        # 使用树形控件实现表格
        treeXueshengMingdan = tkinter.ttk.Treeview(self.frame,
                                                   columns=('col1', 'col2', 'col3'),
                                                   show="headings",
                                                   yscrollcommand=scrollBar.set)
        treeXueshengMingdan.column('col1', width=110, anchor='center')
        treeXueshengMingdan.column('col2', width=90, anchor='center')
        treeXueshengMingdan.column('col3', width=240, anchor='center')
        treeXueshengMingdan.heading('col1', text='学号')
        treeXueshengMingdan.heading('col2', text='姓名')
        treeXueshengMingdan.heading('col3', text='点名时间')
        treeXueshengMingdan.pack(side=tkinter.LEFT, fill=tkinter.Y)
        # 树形控件与垂直滚动条结合
        scrollBar.config(command=treeXueshengMingdan.yview)




def  buttonchakanClick():
    if int_windowchakan.get() == 0:
        int_windowchakan.set(1)
        w1 = windowChuQinchakan(root, '出勤记录查看')
        buttonchuqinchakan.wait_window(w1.top)
        int_windowchakan.set(0)



buttonchuqinchakan = tkinter.Button(root,  text='出勤记录查看',background=backtype, font=fonttype,
                                                command=buttonchakanClick)



##===============================数据查看功能代码结束=======================================##






##==============================学生提问情况查看===========================================##
# 提问情况查看     学号，姓名，问题 ，提问时间     通过，班级筛选
int_windowXueshengTiwenQingkuang = tkinter.IntVar(root, value=0)


class windowXueshengTiwenQingkuang:
    def __init__(self, root, myTitle):
        self.top = tkinter.Toplevel(root, width=680, height=350)
        self.top.resizable(False, False)
        self.top.title(myTitle)
        self.top.attributes('-topmost', 1)
        # 组合框选择专业，或文本框查看特定学号学生的提问情况
        xueshengZhuanye = Common.getZhuanye()
        comboboxZhuanye = tkinter.ttk.Combobox(self.top, values=xueshengZhuanye)
        comboboxZhuanye.place(x=20, y=20, height=20, width=120)


        # 查看指定专业所有同学的提问情况
        def chakanZhuanye():
            zhuanye = comboboxZhuanye.get()
            if not zhuanye:
                tkinter.messagebox.showerror('很抱歉', '请选择专业')
                return
            else:
                conn = sqlite3.connect('database.db')
                cur = conn.cursor()
                cur.execute(
                    "select students.xuehao,students.xingming,xueshengtiwen.wenti,xueshengtiwen.shijian from students,xueshengtiwen "
                    "where students.xuehao=xueshengtiwen.xuehao and students.zhuanye='" + zhuanye + "' order by students.xuehao, xueshengtiwen.shijian desc")
                temp = cur.fetchall()
                conn.close
                # 删除原有的所有行
                for row in treeXueshengMingdan.get_children():
                    treeXueshengMingdan.delete(row)

                for iii, student in enumerate(temp):
                    treeXueshengMingdan.insert('', iii, values=(student[0], student[1], student[2], student[3]))

        buttonZhuanye = tkinter.Button(self.top, text='按专业查看', command=chakanZhuanye)
        buttonZhuanye.place(x=150, y=20, height=20, width=80)



        # 创建表格，设置表头,show="headings"用来隐藏树形控件的默认首列
        self.frame = tkinter.Frame(self.top)
        self.frame.place(x=10, y=50, width=670, height=280)
        # 垂直滚动条
        scrollBar = tkinter.Scrollbar(self.frame)
        scrollBar.pack(side=tkinter.RIGHT, fill=tkinter.Y)
        # 使用树形控件实现表格
        treeXueshengMingdan = tkinter.ttk.Treeview(self.frame,
                                                   columns=('col1', 'col2', 'col3', 'col4'),
                                                   show="headings",
                                                   yscrollcommand=scrollBar.set)
        treeXueshengMingdan.column('col1', width=110, anchor='center')
        treeXueshengMingdan.column('col2', width=80, anchor='center')
        treeXueshengMingdan.column('col3', width=320, anchor='center')
        treeXueshengMingdan.column('col4', width=140, anchor='center')
        treeXueshengMingdan.heading('col1', text='学号')
        treeXueshengMingdan.heading('col2', text='姓名')
        treeXueshengMingdan.heading('col3', text='问题')
        treeXueshengMingdan.heading('col4', text='时间')
        treeXueshengMingdan.pack(side=tkinter.LEFT, fill=tkinter.Y)
        # 树形控件与垂直滚动条结合
        scrollBar.config(command=treeXueshengMingdan.yview)


def buttonChakanTiwenQingkuangClick():

    if int_windowXueshengTiwenQingkuang.get() == 0:
        int_windowXueshengTiwenQingkuang.set(1)
        w1 = windowXueshengTiwenQingkuang(root, '学生主动提问情况')
        buttonXueshengTiwenQingkuang.wait_window(w1.top)
        int_windowXueshengTiwenQingkuang.set(0)


buttonXueshengTiwenQingkuang = tkinter.Button(root, text='提问记录查看',background=backtype,  font=fonttype,command=buttonChakanTiwenQingkuangClick)


##==============================学生提问情况查看结束===========================================##

##================================学生实验验收情况查看=========================================##
# 得分情况查看   分组名单，班级， 得分， 通过实验名筛选
int_windowshiyan = tkinter.IntVar(root, value=0)


class windowshiyanchakan:
    def __init__(self, root, myTitle):
        self.top = tkinter.Toplevel(root, width=530, height=350)
        self.top.resizable(False, False)
        self.top.title(myTitle)
        self.top.attributes('-topmost', 1)
        # 组合框选择专业，或文本框查看特定学号学生的提问情况


        labelshiyan = tkinter.Label(self.top, text='实验名：')
        labelshiyan.place(x=20, y=20, width=80, height=20)


        xueshengShiyan = Common.getShiyan()
        comboboxShiyan = tkinter.ttk.Combobox(self.top, values=xueshengShiyan)
        comboboxShiyan.place(x=90, y=20, height=20, width=120)


        # 查看指定专业所有同学的提问情况
        def chakanshiyan():

            shiyanming=comboboxShiyan.get()
            if  not shiyanming :
                tkinter.messagebox.showerror('很抱歉', '请选择实验名!')
                return
            else:
                conn = sqlite3.connect('database.db')
                cur = conn.cursor()
                cur.execute(
                    "select chengyuan,zhuanye,defen ,filenum ,imagenum from fenzubiao  where shiyanming='"+shiyanming+"'  order by zhuanye desc")   ##desc降序排列asc升序排列
                temp = cur.fetchall()
                conn.close
                # 删除原有的所有行
                for row in treeXueshengMingdan.get_children():
                    treeXueshengMingdan.delete(row)

                for iii, student in enumerate(temp):
                    treeXueshengMingdan.insert('', iii, values=(student[0], student[1], student[2], student[3], student[4]))

        buttonshiyan = tkinter.Button(self.top, text='实验记录查看', background=backtype, command=chakanshiyan)
        buttonshiyan.place(x=230, y=20, height=20, width=100)


        # 创建表格，设置表头,show="headings"用来隐藏树形控件的默认首列
        self.frame = tkinter.Frame(self.top)
        self.frame.place(x=10, y=50, width=510, height=280)
        # 垂直滚动条
        scrollBar = tkinter.Scrollbar(self.frame)
        scrollBar.pack(side=tkinter.RIGHT, fill=tkinter.Y)
        # 使用树形控件实现表格
        treeXueshengMingdan = tkinter.ttk.Treeview(self.frame,
                                                   columns=('col1', 'col2', 'col3', 'col4', 'col5'),
                                                   show="headings",
                                                   yscrollcommand=scrollBar.set)
        treeXueshengMingdan.column('col1', width=120, anchor='center')
        treeXueshengMingdan.column('col2', width=170, anchor='center')
        treeXueshengMingdan.column('col3', width=60, anchor='center')
        treeXueshengMingdan.column('col4', width=80, anchor='center')
        treeXueshengMingdan.column('col5', width=60, anchor='center')
        treeXueshengMingdan.heading('col1', text='小组成员')
        treeXueshengMingdan.heading('col2', text='专业班级')
        treeXueshengMingdan.heading('col3', text='得分')
        treeXueshengMingdan.heading('col4', text='文件作业')
        treeXueshengMingdan.heading('col5', text='截图数')
        treeXueshengMingdan.pack(side=tkinter.LEFT, fill=tkinter.Y)
        # 树形控件与垂直滚动条结合
        scrollBar.config(command=treeXueshengMingdan.yview)

        def refresh():                      ##          刷新显示的数据
            conn = sqlite3.connect('database.db')
            cur = conn.cursor()
            cur.execute(
                "select chengyuan,zhuanye,defen ,filenum,imagenum from fenzubiao  where shiyanming='" + comboboxShiyan.get() + "'  order by zhuanye desc")  ##desc降序排列asc升序排列
            temp = cur.fetchall()
            conn.close
            # 删除原有的所有行
            for row in treeXueshengMingdan.get_children():
                treeXueshengMingdan.delete(row)

            for iii, student in enumerate(temp):
                treeXueshengMingdan.insert('', iii, values=(student[0], student[1], student[2], student[3] ,student[4]))




        ##双击实现加分
        def onDBClick(event):
            selected = treeXueshengMingdan.selection()
            if not selected:
                return

            item = selected[0]
            chengyuanshiyan, zhuanyeshiyan,defen ,filenum,imagenum = treeXueshengMingdan.item(item, 'values')


            # 加分sql语句
            sqlDianming = "update fenzubiao set defen=((select defen from fenzubiao where shiyanming='"+comboboxShiyan.get()+"' and chengyuan='"+chengyuanshiyan+"')+1) where" \
                             "  shiyanming='"+comboboxShiyan.get()+"' and chengyuan='"+chengyuanshiyan+"'"
            Common.doSQL(sqlDianming)
            refresh()

        treeXueshengMingdan.bind("<Double-1>", onDBClick)
        treeXueshengMingdan.pack(side=tkinter.LEFT, fill=tkinter.Y)
        # 树形控件与垂直滚动条结合
        scrollBar.config(command=treeXueshengMingdan.yview)

        # 首先选择一个学生，然后减分
        def buttonJianfenClick():

            selected = treeXueshengMingdan.selection()
            if not selected:
                tkinter.messagebox.showerror('很抱歉', '请选择学生')
                return

            item = selected[0]
            chengyuanshiyan, zhuanyeshiyan, defen, filenum, imagenum = treeXueshengMingdan.item(item, 'values')
            if  defen=='0' :
                tkinter.messagebox.showerror('很抱歉', '他们的分数已经最低了！')
                return

            sqlJiafen ="update fenzubiao set defen=((select defen from fenzubiao where shiyanming='"+comboboxShiyan.get()+"' and chengyuan='"+chengyuanshiyan+"')-1) where" \
                             "  shiyanming='"+comboboxShiyan.get()+"' and chengyuan='"+chengyuanshiyan+"'"
            Common.doSQL(sqlJiafen)
            refresh()


            tkinter.messagebox.showinfo('恭喜', '减分成功')
        buttonJianfen = tkinter.Button(self.top, text='实验不认真减分', command=buttonJianfenClick)
        buttonJianfen.place(x=340, y=20, height=20, width=100)



def  buttonshiyanchakanClick():
    if int_windowshiyan.get() == 0:
        int_windowshiyan.set(1)
        w1 = windowshiyanchakan(root, '学生实验情况')
        buttonshiyanchakan.wait_window(w1.top)
        int_windowshiyan.set(0)



buttonshiyanchakan = tkinter.Button(root,  text='实验记录查看', font=fonttype,background=backtype,
                                                command=buttonshiyanchakanClick)



##============================================学生实验验收情况代码===============================##



## ============================================屏幕广播代码开始=================================##
broadcasting = False


def broadcast(conn):
    global broadcasting
    while broadcasting:
        time.sleep(0.3)
        image = ImageGrab.grab()
        size = image.size

        imageBytes = image.tobytes()
        length = len(imageBytes)

        # 通知将要开始发送截图
        conn.send(b'*****')

        fhead = struct.pack('I32sI',
                            length,
                            str(size).encode(),
                            len(str(size).encode()))
        conn.send(fhead)

        conn.send(imageBytes)
    else:
        conn.send(b'#####')
        conn.close()


def broadcastMain():
    '''广播屏幕截图的主线程函数'''
    global sockBroadCast
    sockBroadCast = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    sockBroadCast.bind(('', 10001))
    sockBroadCast.listen(150)
    while broadcasting:
        try:
            conn, addr = sockBroadCast.accept()
        except:
            sockBroadCast.close()
            return
        threading.Thread(target=broadcast, args=(conn,)).start()
    else:
        sockBroadCast.close()


def onbuttonStartBroadCastClick():
    global broadcasting
    broadcasting = True
    # 启动服务器广播线程
    threading.Thread(target=broadcastMain).start()
    serverIP = socket.gethostbyname(socket.gethostname()) #获取IP地址
    # 通知客户端开始接收广播

    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    sock.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
    IP = serverIP[:serverIP.rindex('.')] + '.255'
    sock.sendto(b'startBroadCast', (IP, 10000))
    buttonStopBroadCast['state'] = 'normal'
    buttonStartBroadCast['state'] = 'disabled'


buttonStartBroadCast = tkinter.Button(root, text='开始屏幕广播',  background=backtype,font=fonttype,command=onbuttonStartBroadCastClick)
buttonStartBroadCast.place(x=240, y=100, height=30, width=100)


def onbuttonStopBroadCastClick():
    global broadcasting
    broadcasting = False
    sockBroadCast.close()
    buttonStopBroadCast['state'] = 'disabled'
    buttonStartBroadCast['state'] = 'normal'


buttonStopBroadCast = tkinter.Button(root, text='结束屏幕广播', background=backtype,font=fonttype, command=onbuttonStopBroadCastClick)
buttonStopBroadCast['state'] = 'disabled'
buttonStopBroadCast.place(x=240, y=140, height=30, width=100)

## ==========================================屏幕广播代码结束============================##


##===========================关闭函数=================================##
def closeWindow():
    # 结束点名
    if int_Dianming.get() == 1:
        int_Dianming.set(0)

    # 结束收作业
    if int_zuoye.get() == 1:
        int_zuoye.set(0)
    if int_wenjianZuoye.get()==1 :
        int_wenjianZuoye.set(0)
    # 结束学生主动提问
    if int_xueshengTiwen.get() == 1:
        int_xueshengTiwen.set(0)

    # 结束屏幕广播
    global broadcasting
    broadcasting = False
    try:
        sockBroadCast.close()
    except:
        pass

    # 教师端关闭时，广播消息通知学生端自动关闭
    sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    sock.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
    IP = serverIP[:serverIP.rindex('.')] + '.255'
    sock.sendto(b'close', (IP, 10000))

    try:

        httpd.server_close()
        httpd.shutdown()
    except:
        pass

    root.destroy()

root.protocol('WM_DELETE_WINDOW', closeWindow)

##========================关闭函数代码结束===========================##




##=============================================主界面切换=================================##


mubar = tkinter.Menu(root)


def mu1():
    buttonStartBroadCast.place(x=240, y=100, height=30, width=100)
    buttonStopBroadCast.place(x=240, y=140, height=30, width=100)
    buttonShuomingWenjian.place(x=240, y=60, height=30, width=100)
    buttonStopJieshouWenjianZuoye.place(x=130, y=60, height=30, width=100)
    buttonStartJieshouWenjianZuoye.place(x=20, y=60, height=30, width=100)
    buttonStartShouzuoye.place(x=20, y=140, height=30, width=100)
    buttonStopShouzuoye.place(x=130, y=140, height=30, width=100)
    buttonsuijitiwen.place(x=240, y=20, height=30, width=100)
    buttonJieshouTiwen.place(x=20, y=100, height=30, width=100)
    buttonTingzhiTiwen.place(x=130, y=100, height=30, width=100)
    buttonStopDianming.place(x=130, y=20, height=30, width=100)
    buttonStartDianming.place(x=20, y=20, height=30, width=100)
    buttonIP.place_forget()
    buttonImportXueshengXinxi.place_forget()
    buttonChakanXueshengxinxi.place_forget()
    buttonMergeDatabase.place_forget()
    buttonDaochu.place_forget()
    buttonshiyanchakan.place_forget()
    buttonXueshengTiwenQingkuang.place_forget()
    buttonchuqinchakan.place_forget()
def mu2():
    buttonshiyanchakan.place(x=20, y=20, height=30, width=100)
    buttonXueshengTiwenQingkuang.place(x=20, y=60, height=30, width=100)
    buttonchuqinchakan.place(x=130, y=60, height=30, width=100)
    buttonIP.place(x=240, y=20, height=30, width=100)
    buttonChakanXueshengxinxi.place(x=130, y=20, height=30, width=100)
    buttonImportXueshengXinxi.place_forget()
    buttonStartDianming.place_forget()
    buttonStopDianming.place_forget()
    buttonJieshouTiwen.place_forget()
    buttonTingzhiTiwen.place_forget()
    buttonStopShouzuoye.place_forget()
    buttonStartShouzuoye.place_forget()
    buttonsuijitiwen.place_forget()
    buttonStopJieshouWenjianZuoye.place_forget()
    buttonStartJieshouWenjianZuoye.place_forget()
    buttonShuomingWenjian.place_forget()
    buttonMergeDatabase.place_forget()
    buttonDaochu.place_forget()
    buttonStartBroadCast.place_forget()
    buttonStopBroadCast.place_forget()
    pass


def mu3():
    buttonImportXueshengXinxi.place(x=20, y=20, height=30, width=100)
    buttonMergeDatabase.place(x=240, y=20, height=30, width=100)
    buttonDaochu.place(x=130, y=20, height=30, width=100)
    buttonStopBroadCast.place_forget()
    buttonStartBroadCast.place_forget()
    buttonchuqinchakan.place_forget()
    buttonXueshengTiwenQingkuang.place_forget()
    buttonshiyanchakan.place_forget()
    buttonShuomingWenjian.place_forget()
    buttonStartJieshouWenjianZuoye.place_forget()
    buttonStopJieshouWenjianZuoye.place_forget()
    buttonStopShouzuoye.place_forget()
    buttonStartShouzuoye.place_forget()
    buttonsuijitiwen.place_forget()
    buttonTingzhiTiwen.place_forget()
    buttonJieshouTiwen.place_forget()
    buttonStopDianming.place_forget()
    buttonStartDianming.place_forget()
    buttonIP.place_forget()
    buttonChakanXueshengxinxi.place_forget()

    pass


mubar.add_command(label="常用功能", command=mu1)
mubar.add_command(label="信息查看", command=mu2)
mubar.add_command(label="数据库操作", command=mu3)

root.config(menu=mubar)

##========================主界面切换代码结束============================##





root.mainloop()